package srdb.migration.dbhandler.redwood;

import srdb.migration.MTKException;
import srdb.migration.MigrationToolkit;
import srdb.migration.Utility;
import srdb.migration.common.MTKConstants;
import srdb.migration.common.MTKTransformDDL;
import srdb.migration.schema.metadata.Column;
import srdb.migration.schema.metadata.ColumnList;
import srdb.migration.schema.metadata.Constraint;
import srdb.migration.schema.metadata.DataType;
import srdb.migration.schema.metadata.Domain;
import srdb.migration.schema.metadata.Function;
import srdb.migration.schema.metadata.Grant;
import srdb.migration.schema.metadata.Group;
import srdb.migration.schema.metadata.Index;
import srdb.migration.schema.metadata.ObjectType;
import srdb.migration.schema.metadata.Package;
import srdb.migration.schema.metadata.Procedure;
import srdb.migration.schema.metadata.Role;
import srdb.migration.schema.metadata.Rule;
import srdb.migration.schema.metadata.Synonym;
import srdb.migration.schema.metadata.Table;
import srdb.migration.schema.metadata.Trigger;
import srdb.migration.schema.metadata.User;
import srdb.migration.schema.metadata.View;

import java.util.Iterator;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class PGTransformDDL extends MTKTransformDDL {
    private static final String SPLIT_DELIMETER = "GO/\n";

    public String getCreateScript(Table table) throws MTKException {
        boolean useOraPartitionSyntax = MigrationToolkit.getInstance()
                .getUseOraPartitionSyntax();
        String sql = super.getCreateScript(table);

        if (!useOraPartitionSyntax) {
            sql = sql + ";\n";
        }

        if ((table.isPartitioned())
                && (!MigrationToolkit.isMemberOfPartitionTableList(table))) {
            if ((table.getPartitionType() == MTKConstants.TablePartitionType.HASH)
                    || (table.getSubPartitionType() == MTKConstants.TablePartitionType.HASH)) {
                throw new MTKException(
                        "Hash partition is not supported in EnterpriseDB.");
            }
            if (!(this instanceof EDBTransformDDL)) {
                sql = sql + "GO/\n";
            }

            if (useOraPartitionSyntax)
                sql = sql + getOraPartitionTableDDL(table);
            else {
                sql = sql + getPartitionTableDDL(table);
            }

        }

        sql = sql + ";\n";

        sql = sql + getColumnsComments(table);

        sql = sql + getTableComments(table);

        sql = sql.replaceAll("", "");
        return sql;
    }

    protected String getColumnLine(Column column) {
        String str = "";
        String colName = column.getName();

        if ((!column.isCaseSensitive()) && (colName.indexOf('#') >= 0)) {
            colName = "\"" + colName.toLowerCase() + "\"";
        }

        str = str + getIndent() + colName;

        String customType = null;

        if ((customType = super.getCustomColType(column.getTableName(),
                column.getName())) != null) {
            str = str + " " + customType;
        } else {
            if (column.isRowID()) {
                str = str + " " + "VARCHAR";
            } else if (column.isInterval())
                str = str + " " + "INTERVAL";
            else if (column.isTimeStamp()) {
                str = str + " "
                        + column.getDataTypeString().replaceAll("LOCAL", "");
            } else if ((column.isNumeric())
                    && (column.getDataTypeString().equalsIgnoreCase("NUMBER") || column
                    .isBinaryFloat())) {
                str = str + " NUMERIC";
            } else if ((column.isNumeric())
                    && (column.getDataTypeString().equalsIgnoreCase("DEC"))) {
                str = str + " DECIMAL";
            } else if ((column.isVarchar())
                    && ((column.getDataTypeString()
                    .equalsIgnoreCase("VARCHAR2")) || (column
                    .getDataTypeString()
                    .equalsIgnoreCase("CHAR VARYING")))) {
                str = str + " VARCHAR";
            } else if (column.isText())
                str = str + " TEXT";
            else if (column.isLargeObject())
                str = str + " BYTEA";
            else {
                str = str + " " + column.getDataTypeString();
            }

            if ((column.getDataType() == Column.DataType.VARCHAR)
                    || (column.getDataType() == Column.DataType.NVARCHAR)) {
                str = str + "(" + column.getLength() + ")";
            }
            if (((column.getDataType() == Column.DataType.INTEGER) || (column
                    .getDataType() == Column.DataType.NUMERIC))
                    && (column.getPrecision() > 0)) {
                str = str + "(" + column.getPrecision();
                if (column.getScale() > 0)
                    str = str + "," + column.getScale();
                str = str + ")";
            }

        }

        if (column.getDefaultClause() != null) {

            // mod by kevinsiki 2014-09-10
//			if (column.getDefaultClause().equals("''")
//					&& column.getDataType() != Column.DataType.VARCHAR
//					&& column.getDataType() != Column.DataType.NVARCHAR
//					&& column.getDataType() != Column.DataType.TEXT) {
//				// type is date ,but default value is empty string. skip
//			} else {
            str = str + " DEFAULT " + column.getDefaultClause();
//			}
        }

        if (!column.isNullable()) {
            str = str + " NOT NULL";
        }
        return str;
    }

    /**
     * 存储过程的转换，ora2srdb，根据语法规则进行替换成srdb的语法。兼容pgsql即可
     *
     * @param proc
     * @return
     */
    public String getCreateScript(Procedure proc) {
//		throw new UnsupportedOperationException(
//				"Procedure migration from Oracle to Postgres is not supported.");

//        String sql = proc.getSQL();
//
//        sql = sql.replaceFirst("AUTHID", "-- AUTHID");
//        sql = "CREATE " + sql + "\n";
//
//		System.out.println(sql);
//        return sql;


//        System.out.println(proc.getSQL());

        //合并为一行有助于正则的匹配
//        String oraSql = proc.getSQL().replace("\n", " \\x01 ");
//        String fname = "";
//        String ret = "";
//        String desc = "";
//        String body = "";
//        String funRegx = "PROCEDURE\\b\\s*(.*)\\s*[return]?\\b(.*?)\\b\\s*[a|i]s\\b\\s*(.*?)\\b\\s*begin\\b\\s*(.*)\\b\\s*end.*";
//        Pattern p = Pattern.compile(funRegx, Pattern.CASE_INSENSITIVE);
//        Matcher m = p.matcher(oraSql);
//        while (m.find()) {
//            fname = m.group(1).replace("\\x01", "\n");
//            ret = m.group(2).replace("\\x01", "\n");
//            desc = m.group(3).replace("\\x01", "\n");
//            body = m.group(4).replace("\\x01", "\n");
//        }
//
//        //方法名和方法体为空时，返回空
//        if (fname.trim().isEmpty() && body.trim().isEmpty()) {
//            return "";
//        }
//        //declare 区域，integer 不能添加（num），例如integer(2) ，srdb里是不合法的，去掉括号
//        desc = Utility.replaceAll(desc, "\\binteger\\b\\(\\d+\\)", "integer");
//
//
//        StringBuffer sql = new StringBuffer("CREATE OR REPLACE FUNCTION ");
//        String funcSql = "";
//        //如果name没有参数，需添加括号；
//        if (fname.replace("\n"," ").indexOf("(")!=-1){
////            sql.append(fname).append("\n");
//            //解析入参和出参 return块、desclre块均依赖，return 后面跟out 的类型，如果是多个out 则用 RETURNS SETOF RECORD
//            if (fname.replace("\n"," ").split("out").length-1>1){//返回多列的
//                //调换in、out位置
//                Pattern tp = Pattern.compile("(.*)\\b\\s*([in|out|in\\s*out|]*)\\b\\s*(.*)\\b", Pattern.CASE_INSENSITIVE);
//                Matcher tm = tp.matcher(fname);
//                while (tm.find()) {
//                    System.out.println( "================ "+tm.group(1));
//                }
//                sql.append(" RETURNS SETOF RECORD");
//
//            }else if (fname.replace("\n"," ").split("out").length-1==1){//返回单列
//
//            }else{
//                sql.append(" RETURNS  void ");
//            }
//
//        }else {
//            sql.append(fname).append("()\n");
//            sql.append(" RETURNS  void ");
//        }
//
//
////        //原先没有返回值的添加 return void
////        if (ret.trim().isEmpty()) {
////            sql.append(" RETURNS  void ").append(ret);
////        } else {
////            sql.append(" RETURNS ").append(ret);
////        }
//
//
//        sql.append(" AS \n");
//        sql.append(" $BODY$ \n");
//        if (!desc.trim().isEmpty() || desc.trim().equals(" ")) {
//
//            sql.append(" DECLARE \n");
//            sql.append(desc).append("\n");
//        }
//
//        sql.append(" BEGIN \n");
//        sql.append(body).append("\n");
//        sql.append(" END; \n");
//        sql.append(" $BODY$ \n");
//        sql.append(" LANGUAGE 'plsrsql' VOLATILE; ");
//        funcSql = sql.toString();
//        sql.setLength(0);
//        /**
//         * 类型处理
//         * ora2srType 根据映射列表替换
//         */
//
//        Iterator iter = Utility.ora2srType.entrySet().iterator();
//        while (iter.hasNext()) {
//            Map.Entry entry = (Map.Entry) iter.next();
//            String key = (String) entry.getKey();
//            String val = (String) entry.getValue();
//            funcSql = Utility.replaceAll(funcSql, "\\b" + key + "\\b", val);
//
//        }
//
//        return funcSql;
        return null;
    }

    //mod by kevin 2015年04月28日16:02:47

    /**
     * function 兼容，ora2srdb，兼容pgslq即可
     *
     * @param func
     * @return
     */
    public String getCreateScript(Function func) {

//        System.out.println(func.getSQL());
        //合并为一行有助于正则的匹配
        String oraSql = func.getSQL().replace("\n", " \\x01 ");
        String fname = "";
        String ret = "";
        String desc = "";
        String body = "";
        String funRegx = "function\\s*(.*?)\\s*return(.*?)\\s*[a|i]s\\s*(.*?)begin\\s*(.*)\\s*end.*";
        Pattern p = Pattern.compile(funRegx, Pattern.CASE_INSENSITIVE);
        Matcher m = p.matcher(oraSql);
        while (m.find()) {
            fname = m.group(1).replace("\\x01", "\n");
            ret = m.group(2).replace("\\x01", "\n");
            desc = m.group(3).replace("\\x01", "\n");
            body = m.group(4).replace("\\x01", "\n");
        }

        //方法名和方法体为空时，返回空
        if (fname.trim().isEmpty() && body.trim().isEmpty()) {
            return "";
        }
        //declare 区域，integer 不能添加（num），例如integer(2) ，srdb里是不合法的，去掉括号
        desc = Utility.replaceAll(desc, "\\binteger\\b\\(\\d+\\)", "integer");


        StringBuffer sql = new StringBuffer("CREATE OR REPLACE FUNCTION ");
        String funcSql = "";
        sql.append(fname).append("\n");
        sql.append(" RETURNS ").append(ret).append(" AS \n");
        sql.append(" $BODY$ \n");
        if (!desc.trim().isEmpty() || desc.trim().equals(" ")) {

            sql.append(" DECLARE \n");
            sql.append(desc).append("\n");
        }

        sql.append(" BEGIN \n");
        sql.append(body).append("\n");
        sql.append(" END; \n");
        sql.append(" $BODY$ \n");
        sql.append(" LANGUAGE 'plsrsql' VOLATILE; ");
        funcSql = sql.toString();
        sql.setLength(0);
        /**
         * 类型处理
         * ora2srType 根据映射列表替换
         */

        Iterator iter = Utility.ora2srType.entrySet().iterator();
        while (iter.hasNext()) {
            Map.Entry entry = (Map.Entry) iter.next();
            String key = (String) entry.getKey();
            String val = (String) entry.getValue();
            funcSql = Utility.replaceAll(funcSql, "\\b" + key + "\\b", val);

        }


//        System.out.println(funcSql);
        return funcSql;

    }

    public String getCreateScript(View view) {
        String sql = super.getCreateScript(view);

        sql = sql.replaceAll("WITH READ ONLY", " ");
        return sql + ";\n";
    }

    public String getCreateScript(Synonym syn) {
        throw new UnsupportedOperationException(
                "Public Synonym migration from Oracle to Postgres is not supported.");
    }

    public String getCreateScript(Trigger trigger) {
        throw new UnsupportedOperationException(
                "Trigger migration from Oracle to Postgres is not supported.");
    }

    public String getCreateScript(Constraint constraint) {
        String sql = "";

        String constTableName = constraint.getTableName();

        if (constraint.getType() == 'P') {
            sql = sql + "ALTER TABLE " + constTableName + " ADD CONSTRAINT "
                    + constraint.getName(true) + " PRIMARY KEY (";

            for (int i = 0; i < constraint.getColumns().size(); i++) {
                sql = sql + constraint.getColumns().get(i).getName();
                if (i < constraint.getColumns().size() - 1)
                    sql = sql + ", ";
            }
            sql = sql + ");\n";
        }

        if (constraint.getType() == 'U') {
            sql = sql + "ALTER TABLE " + constTableName + " ADD CONSTRAINT "
                    + constraint.getName(true) + " UNIQUE (";

            for (int i = 0; i < constraint.getColumns().size(); i++) {
                sql = sql + constraint.getColumns().get(i).getName();
                if (i < constraint.getColumns().size() - 1)
                    sql = sql + ", ";
            }
            sql = sql + ");\n";
        }

        if (constraint.getType() == 'C') {
            if (!constraint.getSearchCondition().endsWith("IS NOT NULL")) {
                sql = sql + "ALTER TABLE " + constTableName
                        + " ADD CONSTRAINT " + constraint.getName(true)
                        + " CHECK (" + constraint.getSearchCondition();

                sql = sql + ");\n";
            }
        }

        if (constraint.getType() == 'R') {
            sql = sql + "ALTER TABLE " + constTableName + " ADD CONSTRAINT "
                    + constraint.getName(true) + " FOREIGN KEY (";

            for (int i = 0; i < constraint.getColumns().size(); i++) {
                sql = sql + constraint.getColumns().get(i).getName();
                if (i < constraint.getColumns().size() - 1)
                    sql = sql + ", ";
            }
            sql = sql + ") REFERENCES "
                    + constraint.getRefIntegritySchemaQualifiedTableName()
                    + " (";
            for (int i = 0; i < constraint.getRefIntegrityColumns().size(); i++) {
                sql = sql
                        + constraint.getRefIntegrityColumns().get(i).getName();
                if (i < constraint.getRefIntegrityColumns().size() - 1)
                    sql = sql + ", ";
            }
            sql = sql + ") MATCH FULL ON DELETE " + constraint.getDeleteRule()
                    + ";\n";
        }

        return replaceNames(sql, getSourceToTargetNames());
    }

    public String getCreateScript(Index index) {
        String sql = "";
        String un_text = "";

        if (index.isUnique()) {
            un_text = "UNIQUE";
        }
        String indexTableName = index.getTableName();
        if (Utility.isCaseSensitive(indexTableName,
                MTKConstants.DATABASE_TYPE.ORACLE)) {
            indexTableName = "\"" + indexTableName + "\"";
        }

        sql = sql + "CREATE " + un_text + " INDEX " + index.getName() + " ON "
                + index.getIndexTableOwner() + "." + indexTableName + " (";

        for (int i = 0; i < index.getColumns().size(); i++) {
            sql = sql + index.getColumns().get(i).getName();
            if (i < index.getColumns().size() - 1)
                sql = sql + ", ";
        }
        sql = sql + ");\n";

        return replaceNames(sql, getSourceToTargetNames());
    }

    public String getCreateScriptPackageSpec(Package pack) {
        throw new UnsupportedOperationException(
                "Package migration from Oracle to Postgres is not supported.");
    }

    public String getCreateScriptPackageBody(Package pack) {
        throw new UnsupportedOperationException(
                "Package migration from Oracle to Postgres is not supported.");
    }

    protected String getColumnsComments(Table table) {
        String sql = "";

        if (table.getColumns() != null) {
            for (int i = 0; i < table.getColumns().size(); i++) {
                if (table.getColumns().get(i).getComments() != null) {
                    sql = sql
                            + "COMMENT ON COLUMN "
                            + table.getTargetSchemaQualifiedName()
                            + "."
                            + table.getColumns().get(i).getName()
                            + " IS '"
                            + table.getColumns().get(i).getComments()
                            .replaceAll("'", "''") + "';\n";
                }

            }

        }

        return sql;
    }

    protected String getTableComments(Table table) {
        String sql = "";

        if (table.getComments() != null) {
            sql = sql + "COMMENT ON TABLE "
                    + table.getTargetSchemaQualifiedName() + " IS '"
                    + table.getComments().replaceAll("'", "\\\\'") + "';\n";
        }

        return sql;
    }

    public String getCreateScript(Role role) {
        String sql = "";
        sql = sql + "Create role " + role.getRoleName() + " with NOLOGIN";

        if (role.isDba()) {
            sql = sql + " SUPERUSER ";
        }

        sql = sql + ";";

        return sql;
    }

    public String getCreateScript(User user) {
        String sql = "";
        sql = sql + "Create user " + user.getName() + " with ";
        if (user.isDba()) {
            sql = sql + " SUPERUSER ";
        }
        sql = sql + " LOGIN;";
        return sql;
    }

    protected String getOraPartitionTableDDL(Table table) {
        StringBuilder partitionTableDDL = new StringBuilder();

        partitionTableDDL.append("PARTITION BY ")
                .append(table.getPartitionType()).append("(")
                .append(table.getPartitionKeyCols().get(0).getName())
                .append(")");

        if (table.getSubPartitions() != null) {
            partitionTableDDL.append("SUBPARTITION BY ")
                    .append(table.getSubPartitionType()).append("(")
                    .append(table.getSubPartitionKeyCols().get(0).getName())
                    .append(")");
        }

        partitionTableDDL.append("( \n");

        for (Table.TablePartition tablePartition : table.getPartitions()) {
            String partitionHighValue = tablePartition.getPartitionHighValue();

            if (tablePartition.getPartitionHighValue().indexOf(
                    ", 'NLS_CALENDAR=GREGORIAN'") > 1) {
                partitionHighValue = partitionHighValue.replaceAll(
                        ", 'NLS_CALENDAR=GREGORIAN'", "");
            }

            if (table.getPartitionType().toString().equalsIgnoreCase("LIST")) {
                partitionTableDDL.append("PARTITION ")
                        .append(tablePartition.getPartitionName())
                        .append(" VALUES(").append(partitionHighValue)
                        .append(")");

                if ((table.getSubPartitionType() != null)
                        && (table.getSubPartitionType().toString()
                        .equalsIgnoreCase("RANGE")))
                    partitionTableDDL.append(getOraSubPartitionTableDDL(table,
                            tablePartition.getPartitionName()));
            } else {
                partitionTableDDL.append("PARTITION ")
                        .append(tablePartition.getPartitionName())
                        .append(" VALUES LESS THAN(")
                        .append(partitionHighValue).append(")");

                if ((table.getSubPartitionType() != null)
                        && (table.getSubPartitionType().toString()
                        .equalsIgnoreCase("LIST"))) {
                    partitionTableDDL.append(getOraSubPartitionTableDDL(table,
                            tablePartition.getPartitionName()));
                }
            }

            partitionTableDDL.append(",");
        }

        partitionTableDDL.replace(partitionTableDDL.length() - 1,
                partitionTableDDL.length(), "");
        partitionTableDDL.append("\n);");

        return partitionTableDDL.toString();
    }

    protected String getOraSubPartitionTableDDL(Table table,
                                                String partitionName) {
        StringBuilder subPartitionTableDDL = new StringBuilder();
        subPartitionTableDDL.append("( ");

        for (Table.TablePartitionSubPartition tableSubPartition : table
                .getSubPartitions()) {
            String partitionHighValue = tableSubPartition
                    .getSubPartitionHighValue();

            if (tableSubPartition.getSubPartitionHighValue().indexOf(
                    ", 'NLS_CALENDAR=GREGORIAN'") > 1) {
                partitionHighValue = partitionHighValue.replaceAll(
                        ", 'NLS_CALENDAR=GREGORIAN'", "");
            }

            if (tableSubPartition.getPartitionName().equalsIgnoreCase(
                    partitionName)) {
                if (table.getSubPartitionType().toString()
                        .equalsIgnoreCase("LIST"))
                    subPartitionTableDDL.append("SUBPARTITION ")
                            .append(tableSubPartition.getSubPartitionName())
                            .append(" VALUES(").append(partitionHighValue)
                            .append(")");
                else {
                    subPartitionTableDDL.append("SUBPARTITION ")
                            .append(tableSubPartition.getSubPartitionName())
                            .append(" VALUES LESS THAN(")
                            .append(partitionHighValue).append(")");
                }

                subPartitionTableDDL.append(",");
            }
        }

        subPartitionTableDDL.replace(subPartitionTableDDL.length() - 1,
                subPartitionTableDDL.length(), "");
        subPartitionTableDDL.append(")");
        return subPartitionTableDDL.toString();
    }

    protected String getPartitionTableDDL(Table table) {
        StringBuilder partitionTableDDL = new StringBuilder();

        for (Table.TablePartition tablePartition : table.getPartitions()) {
            partitionTableDDL.append(constructPartitionTableSQL(
                    table.getPartitionType(), tablePartition,
                    table.getPartitionKeyCols(), table.getName()));
            if (!(this instanceof EDBTransformDDL)) {
                partitionTableDDL.append("GO/\n");
            }

        }

        partitionTableDDL.append(constructPartitionTriggerSQL(table));
        return partitionTableDDL.toString();
    }

    private String constructPartitionTableSQL(
            MTKConstants.TablePartitionType tablePartitionType,
            Table.TablePartition tablePartition,
            ColumnList partitionKeyColsList, String tableName) {
        StringBuilder partitionTableSQL = new StringBuilder();
        partitionTableSQL.append("CREATE TABLE ").append(tableName).append("_");
        partitionTableSQL.append(tablePartition.getPartitionName())
                .append(" (");

        if ((tablePartitionType != MTKConstants.TablePartitionType.RANGE)
                || (!tablePartition.getPartitionHighValue().equalsIgnoreCase(
                "MAXVALUE"))) {
            String condition = constructPartitionClause(tablePartitionType,
                    tablePartition, partitionKeyColsList, false, false);

            if (!condition.equals("")) {
                partitionTableSQL.append(" CHECK (").append(condition)
                        .append(")");
            }

        }

        partitionTableSQL.append(") INHERITS (").append(tableName)
                .append(");\n");
        return partitionTableSQL.toString();
    }

    private String constructPartitionTriggerSQL(Table table) {
        StringBuilder partitionTriggerSQL = new StringBuilder();
        String tableName = table.getName();
        if ((this instanceof EDBTransformDDL)) {
            partitionTriggerSQL.append("CREATE OR REPLACE TRIGGER ")
                    .append(tableName)
                    .append("_PART_TRIG \n BEFORE INSERT\nON ")
                    .append(tableName).append("\n FOR EACH ROW\nBEGIN\n");

            ColumnList tableColList = table.getColumns();
            StringBuilder tableColListStr = new StringBuilder();
            StringBuilder tableColValListStr = new StringBuilder();
            for (int i = 0; i < tableColList.size(); i++) {
                if (i > 0) {
                    tableColListStr.append(",");
                    tableColValListStr.append(",");
                }

                tableColListStr.append(tableColList.get(i).getName());
                tableColValListStr.append(":NEW.").append(
                        tableColList.get(i).getName());
            }

            short partitionCount = 0;
            boolean maxValuePartition = false;

            boolean defaultCase = false;
            Table.TablePartition defaultTablePartition = null;
            int totalPartitions = table.getPartitions().size();

            for (Table.TablePartition tablePartition : table.getPartitions()) {
                partitionCount = (short) (partitionCount + 1);

                String partitionHighValue = tablePartition
                        .getPartitionHighValue();
                if ((partitionHighValue != null)
                        && (partitionHighValue.indexOf("'") == -1)
                        && (partitionHighValue.equalsIgnoreCase("DEFAULT"))) {
                    defaultCase = true;
                    defaultTablePartition = tablePartition;
                    continue;
                }

                if ((table.getPartitionType() == MTKConstants.TablePartitionType.RANGE)
                        && (tablePartition.getPartitionHighValue()
                        .equalsIgnoreCase("MAXVALUE"))) {
                    maxValuePartition = true;
                }

                if (partitionCount == 1) {
                    if ((!maxValuePartition) || (totalPartitions != 1))
                        partitionTriggerSQL.append("\tIF ");
                } else if (maxValuePartition)
                    partitionTriggerSQL.append("\tELSE");
                else {
                    partitionTriggerSQL.append("\tELSIF ");
                }

                if (maxValuePartition) {
                    partitionTriggerSQL.append("\n\t\tINSERT INTO ")
                            .append(table.getTargetSchemaQualifiedName())
                            .append("_")
                            .append(tablePartition.getPartitionName())
                            .append(" (");
                } else {
                    partitionTriggerSQL.append(constructPartitionClause(
                            table.getPartitionType(), tablePartition,
                            table.getPartitionKeyCols(), false, true));
                    partitionTriggerSQL.append("\n\tTHEN\n\t\tINSERT INTO ")
                            .append(table.getTargetSchemaQualifiedName())
                            .append("_")
                            .append(tablePartition.getPartitionName())
                            .append(" (");
                }

                partitionTriggerSQL.append(tableColListStr).append(
                        ")\n\t\tVALUES (");
                partitionTriggerSQL.append(tableColValListStr).append(");\n");

                if (maxValuePartition) {
                    if (totalPartitions > 1)
                        partitionTriggerSQL.append("\n\tEND IF;\n");
                } else if (partitionCount == table.getPartitions().size()) {
                    partitionTriggerSQL
                            .append("\n\tELSE\n\t\tDBMS_OUTPUT.PUT_LINE('Partition key does not map to a partition.');\n\tEND IF;\n");
                }

            }

            if (defaultCase) {
                partitionTriggerSQL.append("\n\tELSE\n");
                partitionTriggerSQL.append("\n\t\tINSERT INTO ")
                        .append(table.getTargetSchemaQualifiedName())
                        .append("_")
                        .append(defaultTablePartition.getPartitionName())
                        .append(" (");

                partitionTriggerSQL.append(tableColListStr).append(
                        ")\n\t\tVALUES (");
                partitionTriggerSQL.append(tableColValListStr).append(");\n");
                partitionTriggerSQL.append("\n\tEND IF;\n");
            }

            partitionTriggerSQL.append("\n\tRETURN NULL;\nEND;\n");
        } else {
            partitionTriggerSQL.append("CREATE OR REPLACE FUNCTION ")
                    .append(tableName)
                    .append("_PART_TRIG_FUNC() RETURNS TRIGGER as $$ \n")
                    .append("BEGIN \n");

            ColumnList tableColList = table.getColumns();
            StringBuilder tableColListStr = new StringBuilder();
            StringBuilder tableColValListStr = new StringBuilder();
            for (int i = 0; i < tableColList.size(); i++) {
                if (i > 0) {
                    tableColListStr.append(",");
                    tableColValListStr.append(",");
                }

                tableColListStr.append(tableColList.get(i).getName());
                tableColValListStr.append("NEW.").append(
                        tableColList.get(i).getName());
            }

            short partitionCount = 0;
            boolean maxValuePartition = false;

            boolean defaultCase = false;
            Table.TablePartition defaultTablePartition = null;
            int totalPartitions = table.getPartitions().size();

            for (Table.TablePartition tablePartition : table.getPartitions()) {
                partitionCount = (short) (partitionCount + 1);

                String partitionHighValue = tablePartition
                        .getPartitionHighValue();
                if ((partitionHighValue != null)
                        && (partitionHighValue.indexOf("'") == -1)
                        && (partitionHighValue.equalsIgnoreCase("DEFAULT"))) {
                    defaultCase = true;
                    defaultTablePartition = tablePartition;
                    continue;
                }

                if ((table.getPartitionType() == MTKConstants.TablePartitionType.RANGE)
                        && (tablePartition.getPartitionHighValue()
                        .equalsIgnoreCase("MAXVALUE"))) {
                    maxValuePartition = true;
                }

                if (partitionCount == 1) {
                    if ((!maxValuePartition) || (totalPartitions != 1))
                        partitionTriggerSQL.append("\tIF ");
                } else if (maxValuePartition)
                    partitionTriggerSQL.append("\tELSE");
                else {
                    partitionTriggerSQL.append("\tELSIF ");
                }

                if (maxValuePartition) {
                    partitionTriggerSQL.append("\n\t\tINSERT INTO ")
                            .append(table.getTargetSchemaQualifiedName())
                            .append("_")
                            .append(tablePartition.getPartitionName())
                            .append(" (");
                } else {
                    partitionTriggerSQL.append(constructPartitionClause(
                            table.getPartitionType(), tablePartition,
                            table.getPartitionKeyCols(), false, true));
                    partitionTriggerSQL.append("\n\tTHEN\n\t\tINSERT INTO ")
                            .append(table.getTargetSchemaQualifiedName())
                            .append("_")
                            .append(tablePartition.getPartitionName())
                            .append(" (");
                }

                partitionTriggerSQL.append(tableColListStr).append(
                        ")\n\t\tVALUES (");
                partitionTriggerSQL.append(tableColValListStr).append(");\n");

                if (maxValuePartition) {
                    if (totalPartitions > 1)
                        partitionTriggerSQL.append("\n\tEND IF;\n");
                } else if (partitionCount == table.getPartitions().size()) {
                    partitionTriggerSQL
                            .append("\n\tELSE\n\t\tRAISE NOTICE 'Partition key does not map to a partition.';\n\tEND IF;\n");
                }

            }

            if (defaultCase) {
                partitionTriggerSQL.append("\n\tELSE\n");
                partitionTriggerSQL.append("\n\t\tINSERT INTO ")
                        .append(table.getTargetSchemaQualifiedName())
                        .append("_")
                        .append(defaultTablePartition.getPartitionName())
                        .append(" (");

                partitionTriggerSQL.append(tableColListStr).append(
                        ")\n\t\tVALUES (");
                partitionTriggerSQL.append(tableColValListStr).append(");\n");
                partitionTriggerSQL.append("\n\tEND IF;\n");
            }

            partitionTriggerSQL.append("\n\tRETURN NULL;\nEND;");
            partitionTriggerSQL.append("$$ LANGUAGE PLPGSQL;\n");
            partitionTriggerSQL.append("GO/\n");
            partitionTriggerSQL.append("CREATE TRIGGER ").append(tableName)
                    .append("_PART_TRIG \n BEFORE INSERT \n ON ")
                    .append(tableName)
                    .append("\n FOR EACH ROW EXECUTE PROCEDURE ")
                    .append(tableName).append("_PART_TRIG_FUNC();\n");

            partitionTriggerSQL.append("GO/\n");
        }

        return partitionTriggerSQL.toString();
    }

    private String constructPartitionRulesSQL(Table table) {
        StringBuilder partitionRulesSQL = new StringBuilder();

        String tableName = table.getName();
        partitionRulesSQL.append("CREATE OR REPLACE RULE ").append(tableName)
                .append("_validate_rule AS\n ON INSERT TO ").append(tableName)
                .append("\nWHERE ")
                .append(table.getPartitionKeyCols().get(0).getName())
                .append(" NOT IN (");

        int count = 0;
        for (Table.TablePartition tablePartition : table.getPartitions()) {
            count++;

            if (count > 1) {
                partitionRulesSQL.append(",");
            }

            partitionRulesSQL.append(tablePartition.getPartitionHighValue());
        }

        partitionRulesSQL.append(")\nDO INSTEAD\nNOTHING;\n");

        for (Table.TablePartition tablePartition : table.getPartitions()) {
            partitionRulesSQL
                    .append("CREATE OR REPLACE RULE ")
                    .append(tableName)
                    .append("_")
                    .append(tablePartition.getPartitionName())
                    .append("_rule AS\n ON INSERT TO ")
                    .append(tableName)
                    .append("\nWHERE ")
                    .append(constructPartitionClause(table.getPartitionType(),
                            tablePartition, table.getPartitionKeyCols(), false,
                            false)).append("\nDO INSTEAD\nINSERT INTO ")
                    .append(tableName).append("_")
                    .append(tablePartition.getPartitionName())
                    .append(" VALUES(");

            ColumnList tableColList = table.getColumns();

            for (int i = 0; i < tableColList.size(); i++) {
                if (i > 0) {
                    partitionRulesSQL.append(",");
                }

                partitionRulesSQL.append("NEW.").append(
                        tableColList.get(i).getName());
            }

            partitionRulesSQL.append(");\n");
        }

        return partitionRulesSQL.toString();
    }

    private String constructPartitionClause(
            MTKConstants.TablePartitionType tablePartitionType,
            Table.TablePartition tablePartition,
            ColumnList partitionKeyColsList, boolean negate, boolean prependNew) {
        StringBuilder partitionCriteria = new StringBuilder();
        String partitionValue = tablePartition.getPartitionHighValue();
        String NEW_CLAUSE = (this instanceof EDBTransformDDL) ? ":NEW."
                : "NEW.";

        if ((partitionValue != null) && (partitionValue.indexOf("'") == -1)
                && (partitionValue.equalsIgnoreCase("DEFAULT"))) {
            return "";
        }

        if (tablePartitionType == MTKConstants.TablePartitionType.LIST) {
            Column col = partitionKeyColsList.get(0);

            if (prependNew) {
                partitionCriteria.append(NEW_CLAUSE);
            }

            partitionCriteria.append(col.getName());
            if (negate)
                partitionCriteria.append(" NOT IN (");
            else {
                partitionCriteria.append(" IN (");
            }

            if (partitionValue.startsWith("TO_DATE")) {
                String[] arrPartitionValue = trimToDateNLSArgument(splitToIndividualFuncs(partitionValue));
                partitionValue = arrPartitionValue[0];

                for (int index = 1; index < arrPartitionValue.length; index++) {
                    partitionValue = partitionValue + ","
                            + arrPartitionValue[index];
                }
            }

            partitionCriteria.append(partitionValue).append(")");
        } else if (tablePartitionType == MTKConstants.TablePartitionType.RANGE) {
            String[] arrPartitionValue = null;

            if (isFuncExpression(partitionValue)) {
                arrPartitionValue = splitToIndividualFuncs(partitionValue);

                if (partitionValue.startsWith("TO_DATE")) {
                    arrPartitionValue = trimToDateNLSArgument(arrPartitionValue);
                }

            } else if (partitionKeyColsList.size() > 1) {
                arrPartitionValue = partitionValue.split(",");
            } else {
                arrPartitionValue = new String[1];
                arrPartitionValue[0] = partitionValue;
            }

            StringBuilder colLessThanCriteria = new StringBuilder();
            StringBuilder colEqualityCriteria = null;
            Column col = null;

            for (int index = 0; index < partitionKeyColsList.size(); index++) {
                if (index > 0) {
                    if (index == 1)
                        colEqualityCriteria = new StringBuilder();
                    else {
                        colEqualityCriteria.append(" AND ");
                    }

                    if (prependNew) {
                        colEqualityCriteria.append(NEW_CLAUSE);
                    }

                    colEqualityCriteria.append(col.getName()).append(" = ")
                            .append(arrPartitionValue[(index - 1)]);
                }

                col = partitionKeyColsList.get(index);

                if (prependNew) {
                    colLessThanCriteria.append(NEW_CLAUSE);
                }

                colLessThanCriteria.append(col.getName());
                colLessThanCriteria.append(" < ");

                if ((!arrPartitionValue[index].endsWith(")"))
                        && (arrPartitionValue[index].contains("TO_DATE")))
                    colLessThanCriteria.append(arrPartitionValue[index] + ")");
                else {
                    colLessThanCriteria.append(arrPartitionValue[index]);
                }

                if (index == 0)
                    partitionCriteria.append(colLessThanCriteria);
                else {
                    partitionCriteria.append(" OR (")
                            .append(colEqualityCriteria).append(" AND ")
                            .append(colLessThanCriteria).append(")");
                }

                colLessThanCriteria.setLength(0);
            }
        }
        return partitionCriteria.toString();
    }

    protected boolean isFuncExpression(String funcsCSVString) {
        return funcsCSVString.matches("[A-Za-z]([a-zA-Z0-9_#$])+\\s*\\(.*\\)");
    }

    protected String[] splitToIndividualFuncs(String funcsCSVString) {
        if (isFuncExpression(funcsCSVString)) {
            int startParenthesisIndex = funcsCSVString.indexOf('(');
            String funcName = funcsCSVString
                    .substring(0, startParenthesisIndex);

            String[] arrFuncExpr = funcsCSVString.split(",\\s*" + funcName);

            for (int index = 0; index < arrFuncExpr.length; index++) {
                if (index > 0) {
                    arrFuncExpr[index] = (funcName + arrFuncExpr[index]);
                }
            }

            return arrFuncExpr;
        }

        return new String[]{funcsCSVString};
    }

    protected String trimToDateNLSArgument(String toDateString) {
        String[] arguments = toDateString.split(",");

        if (arguments.length == 3) {
            toDateString = arguments[0] + "," + arguments[1] + ")";
        }

        return toDateString;
    }

    protected String[] trimToDateNLSArgument(String[] arrToDateString) {
        for (int index = 0; index < arrToDateString.length; index++) {
            arrToDateString[index] = trimToDateNLSArgument(arrToDateString[index]);
        }

        return arrToDateString;
    }

    public String getCreateScript(Grant grant) {
        String sql = "GRANT ";

        if (grant.getPrivilegeType() == Grant.PRIV_TYPE.OBJECT) {
            sql = sql + grant.getPrivilege() + " ON "
                    + grant.getOwner().toLowerCase() + "."
                    + grant.getObjectName();
            sql = sql + " TO " + grant.getGrantee() + ";\n";

            sql = sql + "GRANT USAGE ON SCHEMA "
                    + grant.getOwner().toLowerCase() + " TO "
                    + grant.getGrantee() + ";";
        } else {
            sql = sql + grant.getObjectName() + " TO " + grant.getGrantee()
                    + ";\n";
        }

        return sql;
    }

    public String getCreateScript(ObjectType objectType) {
        throw new UnsupportedOperationException(
                "Object Type migration from Oracle to Postgres is not supported.");
    }

    public String getCreateScript(Rule rule) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public String getCreateScript(Group group) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public String getCreateScript(Domain domain) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public String getCreateScript(DataType type) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public String plsqlToPlsrsql(String sql) {


        return "";
    }
}

/*

 */